package br.com.digidata.modelo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.digidata.negocio.Agenda;
import br.com.digidata.negocio.Medico;
import br.com.digidata.negocio.Paciente;

public class AgendaDAO {
	private Connection conn;
	
	public boolean insert(Agenda a) {
        conn = ConexaoBD.getConnection();
        boolean retorno = false;

		try {
			PreparedStatement stmt = conn.prepareStatement("INSERT INTO agenda " +
					"(data, hora, status, medico_id, paciente_id) " +
					"values (?,?,?,?,?)");
			
            stmt.setString(1, a.getData());
            stmt.setString(2, a.getHora());
            stmt.setString(3, a.getStatus());
            stmt.setInt(4, a.getMedico().getId());
            stmt.setInt(5, a.getPaciente().getId());
           
			stmt.executeUpdate();
			
		    retorno = true;
			
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println("Incluir agenda: " + new java.util.Date() + " " + e);
			retorno = false;
		}
		return retorno;
	}

	public void update(Agenda a) {
		try{
            conn = ConexaoBD.getConnection();
            
            PreparedStatement stmt = conn.prepareStatement("UPDATE agenda SET " +
            		"data = ?, hora = ?, status = ?, medico_id = ?, paciente_id = ? WHERE id = ? ");
            stmt.setString(1, a.getData());
            stmt.setString(2, a.getHora());
            stmt.setString(3, a.getStatus());
            stmt.setInt(4, a.getMedico().getId());
            stmt.setInt(5, a.getPaciente().getId());
            stmt.setInt(6, a.getId());
            stmt.executeUpdate();
            
            stmt.close();
            conn.close();
            
        } catch (Exception e){
            System.out.println("Alterar agenda: " + new java.util.Date() + " " + e);
        }
	}
	
	public List<Agenda> retrive(){
        List<Agenda> agendamentos = new ArrayList<Agenda>();
        
        try {
            conn = ConexaoBD.getConnection();
        	
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * FROM agenda a " +
            		"JOIN paciente p ON a.paciente_id = p.id " +
            		"JOIN medico m ON a.medico_id = m.id");
            
            while (rs.next()) {
            	Medico m = new Medico(rs.getInt("m.id"), 
                		rs.getString("m.cpf"), 
                		rs.getString("m.nome"), 
                		rs.getString("m.data_nascimento"), 
                		rs.getString("m.sexo"),
                		rs.getString("m.telefone_residencial"),
                		rs.getString("m.telefone_celular"), 
                		null, 
                		rs.getInt("crm"), 
                		rs.getString("especialidade"));
            	
            	Paciente p = new Paciente(rs.getInt("p.id"), 
                		rs.getString("p.cpf"), 
                		rs.getString("p.nome"), 
                		rs.getString("p.data_nascimento"), 
                		rs.getString("p.sexo"),
                		rs.getString("p.telefone_residencial"),
                		rs.getString("p.telefone_celular"), 
                		null, 
                		rs.getInt("identidade"), 
                		rs.getString("convenio"));
            	
                Agenda a = new Agenda(rs.getInt("id"),
                		rs.getString("data"), 
                		rs.getString("hora"),
                		rs.getString("status"),
                		m, p);
            	
                agendamentos.add(a);
            }
            
            rs.close();
            stmt.close();
            conn.close();
            
        } catch (Exception e) {
            System.out.println("Listar agenda: " + new java.util.Date() + " " + e);
        }
        
        return agendamentos;
    }
	
	public void delete(Agenda a) {
		try{
            conn = ConexaoBD.getConnection();
            
            PreparedStatement stmt = conn.prepareStatement("DELETE FROM agenda WHERE id = ? ");
            stmt.setInt(1, a.getId());
            stmt.executeUpdate();
            
            stmt.close();
            conn.close();
            
        } catch (Exception e){
            System.out.println("Alterar agenda: " + new java.util.Date() + " " + e);
        }		
	}
}
